package org.javaforever.gatescore.poi;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.javaforever.gatescore.core.FrontDomain;
import org.javaforever.gatescore.core.FrontDropdown;
import org.javaforever.gatescore.core.FrontDummyPrism;
import org.javaforever.gatescore.core.FrontEnum;
import org.javaforever.gatescore.core.FrontField;
import org.javaforever.gatescore.core.FrontManyToMany;
import org.javaforever.gatescore.core.FrontPrism;
import org.javaforever.gatescore.core.FrontProject;
import org.javaforever.gatescore.core.FrontType;
import org.javaforever.gatescore.core.Pair;
import org.javaforever.gatescore.core.ValidateInfo;
import org.javaforever.gatescore.exceltrnslator.LayoutExcelTranslator;
import org.javaforever.gatescore.exceltrnslator.ReportExcelTranslator;
import org.javaforever.gatescore.exceltrnslator.ShiroAuthExcelTranslator;
import org.javaforever.gatescore.exception.ValidateException;
import org.javaforever.gatescore.generators.PackageJsonGenerator;
import org.javaforever.gatescore.reports.LayoutComb;
import org.javaforever.gatescore.reports.ReportComb;
import org.javaforever.gatescore.utils.BooleanUtil;
import org.javaforever.gatescore.utils.DomainUtil;
import org.javaforever.gatescore.utils.StringUtil;

public class SpreadSheetTranslater {
	protected FrontProject project;
	protected String username;
	protected String password;
	protected String filename;
	
	protected final static String[] forbiddenwords = { "abstract", "assert", "boolean", "break", "byte", "case",
			"catch", "char", "class", "const", "continue", "default", "do", "double", "else", "enum", "extends",
			"final", "finally", "float", "for", "if",

			"implements", "import", "instanceof", "int", "interface", "long", "native", "new", "package", "private",
			"protected", "public", "return", "short", "static", "strictfp", "super", "switch",

			"synchronized", "this", "throw", "throws", "transient", "try", "void", "volatile", "while", "byValue",
			"cast", "false", "future", "generic", "inner", "operator", "outer", "rest", "true", "var", "goto", "const",
			"null" };

	protected final static String[] sqlKeyWords = { "alter", "and", "as", "asc", "between", "by", "count", "create",
			"delete", "desc", "distinct", "drop", "from", "group", "having", "in", "insert", "into", "is", "join",
			"like", "not", "on", "or", "order", "select", "set", "table", "union", "update", "values", "where", "limit",
			"bool", "boolean", "bit", "blob", "enum", "long", "longblob", "longtext", "medium", "mediumblob",
			"mediumint", "mediumtext", "time", "timestamp", "tinyblob", "tinyint", "tinytext", "text", "bigint", "int",
			"int1", "int2", "int3", "int4", "int8", "integer", "float", "float4", "float8", "double", "char",
			"varbinary", "varchar", "varcharacter", "precision", "real", "date", "datetime", "year", "unsigned",
			"signed", "decimal", "numeric", "false", "true", "null", "unknown", "date", "time", "timestamp" };

	public FrontProject translate(HSSFWorkbook book) throws Exception {
		project = new FrontProject();
		project = translateProjectMetaData(book.getSheet("project"), project);
		if ("simple".equalsIgnoreCase(project.getTechnicalstack())||"tower".equalsIgnoreCase(project.getTechnicalstack())) {
			project.setComputerLanguage("java");
			project.setTechnicalstack("sbmeu");
		}
		List<FrontDomain> domains = new ArrayList<FrontDomain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					FrontDomain domain = translateDomain(sheet);
					domain.setLanguage(project.getLanguage());
					domain.setDbType(project.getDbType());
					domain.setComputerLanguage(project.getComputerLanguage());
					domain.setTechnicalstack(project.getTechnicalstack());
					domain.setFrontendUi(project.getFrontendUi());
					domain.setBackendUi(project.getBackendUi());
					domain.setUseController(project.isUseController());
					domains.add(domain);
				}else if (sheet.getSheetName().toLowerCase().contains("enum")) {
					FrontEnum myenum = translateEnum(sheet);
					myenum.setLanguage(project.getLanguage());
					myenum.setDbType(project.getDbType());
					myenum.setComputerLanguage(project.getComputerLanguage());
					myenum.setTechnicalstack(project.getTechnicalstack());
					myenum.setFrontendUi(project.getFrontendUi());
					myenum.setBackendUi(project.getBackendUi());
					myenum.setUseController(project.isUseController());
					domains.add(myenum);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		
		for (FrontDomain d:domains) {
			if (!StringUtil.isBlank(project.getControllerSuffix())) d.setControllerSuffix(project.getControllerSuffix());	
			if (!StringUtil.isBlank(project.getControllerNamingSuffix())) d.setControllerNamingSuffix(project.getControllerNamingSuffix());
			if (!StringUtil.isBlank(project.getTitle())) d.setTitle(project.getTitle());
			if (!StringUtil.isBlank(project.getStandardName())) d.setProjectName(project.getStandardName());
		}
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("module")) {
					org.javaforever.gatescore.core.Module m = translateModule(project, sheet, domains);
					project.addModule(m);
				}
				if (sheet.getSheetName().toLowerCase().contains("layout")) {
					LayoutComb lcm = translateLayout(project, sheet, domains);
					project.addLayoutComb(lcm);
				}
				if (sheet.getSheetName().toLowerCase().contains("report")) {
					ReportComb rcm = translateReport(project, sheet, domains);
					project.addReportComb(rcm);
				}
			}catch (ValidateException ve) {
				info0.addAllCompileErrors(ve.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(ve.getValidateInfo().getCompileWarnings());
			}
		}
		
		if (!info0.success(false)){
			System.out.println(info0.toString());
			throw new ValidateException(info0);
		}
		
		decorateDropdowns(domains);
		List<FrontDomain> domainList = new ArrayList<>();
		domainList.addAll(domains);
		decorateMtmDomainList(project.getDomains(),domainList);
		project.setDomains(domainList);

		List<FrontPrism> prismList = generatePrismsByDomains(domainList);
		project.setPrisms(prismList);
		
		for (FrontPrism p : project.getPrisms()) {
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (FrontManyToMany mtm:p.getDomain().getManyToManies()){
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}
		project.setPjg(new PackageJsonGenerator(domains.get(0)));
		return project;
	}
	
	public FrontProject mjeeeuTranslate(HSSFWorkbook book) throws Exception {
		project = new FrontProject();
		project = translateProjectMetaData(book.getSheet("project"), project);
		project.setComputerLanguage("java");
		project.setTechnicalstack("mjeeeu");
		List<FrontDomain> domains = new ArrayList<FrontDomain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					FrontDomain domain = translateDomain(sheet);
					domain.setLanguage(project.getLanguage());
					domain.setDbType(project.getDbType());
					domain.setComputerLanguage(project.getComputerLanguage());
					domain.setTechnicalstack(project.getTechnicalstack());
					domain.setFrontendUi(project.getFrontendUi());
					domain.setBackendUi(project.getBackendUi());
					domain.setUseController(true);
					domains.add(domain);
				}else if (sheet.getSheetName().toLowerCase().contains("enum")) {
					FrontEnum myenum = translateEnum(sheet);
					myenum.setLanguage(project.getLanguage());
					myenum.setDbType(project.getDbType());
					myenum.setComputerLanguage(project.getComputerLanguage());
					myenum.setTechnicalstack(project.getTechnicalstack());
					myenum.setFrontendUi(project.getFrontendUi());
					myenum.setBackendUi(project.getBackendUi());
					myenum.setUseController(true);
					domains.add(myenum);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		
		for (FrontDomain d:domains) {
			if (!StringUtil.isBlank(project.getControllerSuffix())) d.setControllerSuffix(project.getControllerSuffix());	
			if (!StringUtil.isBlank(project.getControllerNamingSuffix())) d.setControllerNamingSuffix(project.getControllerNamingSuffix());
			if (!StringUtil.isBlank(project.getTitle())) d.setTitle(project.getTitle());
			if (!StringUtil.isBlank(project.getStandardName())) d.setProjectName(project.getStandardName());
		}
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("module")) {
					org.javaforever.gatescore.core.Module m = translateModule(project, sheet, domains);
					project.addModule(m);
				}
				if (sheet.getSheetName().toLowerCase().contains("layout")) {
					LayoutComb lcm = translateLayout(project, sheet, domains);
					project.addLayoutComb(lcm);
				}
				if (sheet.getSheetName().toLowerCase().contains("report")) {
					ReportComb rcm = translateReport(project, sheet, domains);
					project.addReportComb(rcm);
				}
			}catch (ValidateException ve) {
				info0.addAllCompileErrors(ve.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(ve.getValidateInfo().getCompileWarnings());
			}
		}
		
		if (!info0.success(false)){
			System.out.println(info0.toString());
			throw new ValidateException(info0);
		}
		
		decorateDropdowns(domains);
		List<FrontDomain> domainList = new ArrayList<>();
		domainList.addAll(domains);
		decorateMtmDomainList(project.getDomains(),domainList);
		project.setDomains(domainList);

		List<FrontPrism> prismList = generatePrismsByDomains(domainList);
		project.setPrisms(prismList);
		
		for (FrontPrism p : project.getPrisms()) {
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (FrontManyToMany mtm:p.getDomain().getManyToManies()){
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}
		project.setPjg(new PackageJsonGenerator(domains.get(0)));
		return project;
	}
	
	public FrontProject goTranslate(HSSFWorkbook book) throws Exception {
		project = new FrontProject();
		project = translateProjectMetaData(book.getSheet("project"), project);
		project.setComputerLanguage("go");
		project.setTechnicalstack("simple");
		project.setProjectHome("https://gitee.com/jerryshensjf/Fairchild/");
		List<FrontDomain> domains = new ArrayList<FrontDomain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					FrontDomain domain = translateDomain(sheet);
					String deniedVerbsStr = domain.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					domain.setVerbDeniesStr(deniedVerbsStr);
					domain.setLanguage(project.getLanguage());
					domain.setDbType(project.getDbType());
					domain.setFrontendUi(project.getFrontendUi());
					domain.setBackendUi(project.getBackendUi());
					domain.setComputerLanguage(project.getComputerLanguage());
					domain.setTechnicalstack(project.getTechnicalstack());
					domains.add(domain);
				}else if (sheet.getSheetName().toLowerCase().contains("enum")) {
					FrontEnum myenum = translateEnum(sheet);
					String deniedVerbsStr = myenum.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					myenum.setVerbDeniesStr(deniedVerbsStr);
					myenum.setLanguage(project.getLanguage());
					myenum.setDbType(project.getDbType());
					myenum.setFrontendUi(project.getFrontendUi());
					myenum.setBackendUi(project.getBackendUi());
					myenum.setComputerLanguage(project.getComputerLanguage());
					myenum.setTechnicalstack(project.getTechnicalstack());
					domains.add(myenum);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		
		for (FrontDomain d:domains) {
			if (!StringUtil.isBlank(project.getControllerSuffix())) d.setControllerSuffix(project.getControllerSuffix());	
			if (!StringUtil.isBlank(project.getControllerNamingSuffix())) d.setControllerNamingSuffix(project.getControllerNamingSuffix());
			if (!StringUtil.isBlank(project.getTitle())) d.setTitle(project.getTitle());
			if (!StringUtil.isBlank(project.getStandardName())) d.setProjectName(project.getStandardName());
		}
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("module")) {
					org.javaforever.gatescore.core.Module m = translateModule(project, sheet, domains);
					project.addModule(m);
				}
				if (sheet.getSheetName().toLowerCase().contains("layout")) {
					LayoutComb lcm = translateLayout(project, sheet, domains);
					project.addLayoutComb(lcm);
				}
				if (sheet.getSheetName().toLowerCase().contains("report")) {
					ReportComb rcm = translateReport(project, sheet, domains);
					project.addReportComb(rcm);
				}
			}catch (ValidateException ve) {
				info0.addAllCompileErrors(ve.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(ve.getValidateInfo().getCompileWarnings());
			}
		}
		
		if (!info0.success(true)) {
			System.out.println(info0.toString());
			throw new ValidateException(info0);
		}
		
		decorateDropdowns(domains);
		List<FrontDomain> domainList = new ArrayList<>();
		domainList.addAll(domains);
		decorateMtmDomainList(project.getDomains(),domainList);
		project.setDomains(domainList);

		List<FrontPrism> prismList = generatePrismsByDomains(domainList);
		project.setPrisms(prismList);
		
		for (FrontPrism p : project.getPrisms()) {
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (FrontManyToMany mtm:p.getDomain().getManyToManies()){
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}
		project.setPjg(new PackageJsonGenerator(domains.get(0)));
		return project;
	}
	
	public FrontProject rustTranslate(HSSFWorkbook book) throws Exception {
		project = new FrontProject();
		project = translateProjectMetaData(book.getSheet("project"), project);
		project.setComputerLanguage("rust");
		project.setTechnicalstack("tower");
		project.setProjectHome("https://gitee.com/jerryshensjf/Lotus/");
		List<FrontDomain> domains = new ArrayList<FrontDomain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					FrontDomain domain = translateDomain(sheet);
					String deniedVerbsStr = domain.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")||deniedVerbsStr.equals("")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					domain.setVerbDeniesStr(deniedVerbsStr);
					domain.setLanguage(project.getLanguage());
					domain.setDbType(project.getDbType());
					domain.setFrontendUi(project.getFrontendUi());
					domain.setBackendUi(project.getBackendUi());
					domain.setComputerLanguage(project.getComputerLanguage());
					domain.setTechnicalstack(project.getTechnicalstack());
					domains.add(domain);
				}else if (sheet.getSheetName().toLowerCase().contains("enum")) {
					FrontEnum myenum = translateEnum(sheet);
					String deniedVerbsStr = myenum.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					myenum.setVerbDeniesStr(deniedVerbsStr);
					myenum.setLanguage(project.getLanguage());
					myenum.setDbType(project.getDbType());
					myenum.setFrontendUi(project.getFrontendUi());
					myenum.setBackendUi(project.getBackendUi());
					myenum.setComputerLanguage(project.getComputerLanguage());
					myenum.setTechnicalstack(project.getTechnicalstack());
					domains.add(myenum);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		
		for (FrontDomain d:domains) {
			if (!StringUtil.isBlank(project.getControllerSuffix())) d.setControllerSuffix(project.getControllerSuffix());	
			if (!StringUtil.isBlank(project.getControllerNamingSuffix())) d.setControllerNamingSuffix(project.getControllerNamingSuffix());
			if (!StringUtil.isBlank(project.getTitle())) d.setTitle(project.getTitle());
			if (!StringUtil.isBlank(project.getStandardName())) d.setProjectName(project.getStandardName());
		}
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("module")) {
					org.javaforever.gatescore.core.Module m = translateModule(project, sheet, domains);
					project.addModule(m);
				}
				if (sheet.getSheetName().toLowerCase().contains("layout")) {
					LayoutComb lcm = translateLayout(project, sheet, domains);
					project.addLayoutComb(lcm);
				}
				if (sheet.getSheetName().toLowerCase().contains("report")) {
					ReportComb rcm = translateReport(project, sheet, domains);
					project.addReportComb(rcm);
				}
			}catch (ValidateException ve) {
				info0.addAllCompileErrors(ve.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(ve.getValidateInfo().getCompileWarnings());
			}
		}
		
		if (!info0.success(true)) {
			System.out.println(info0.toString());
			throw new ValidateException(info0);
		}
		
		decorateDropdowns(domains);
		List<FrontDomain> domainList = new ArrayList<>();
		domainList.addAll(domains);
		decorateMtmDomainList(project.getDomains(),domainList);
		project.setDomains(domainList);

		List<FrontPrism> prismList = generatePrismsByDomains(domainList);
		project.setPrisms(prismList);
		
		for (FrontPrism p : project.getPrisms()) {
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (FrontManyToMany mtm:p.getDomain().getManyToManies()){
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}
		
		project.setPjg(new PackageJsonGenerator(domains.get(0)));
		return project;
	}
	
	public FrontProject zigTranslate(HSSFWorkbook book) throws Exception {
		project = new FrontProject();
		project = translateProjectMetaData(book.getSheet("project"), project);
		project.setComputerLanguage("zig");
		project.setTechnicalstack("light");
		project.setUseOnlyJson(true);
		project.setProjectHome("https://gitee.com/jerryshensjf/Logic/");
		List<FrontDomain> domains = new ArrayList<FrontDomain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					FrontDomain domain = translateDomain(sheet);
					String deniedVerbsStr = domain.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")||deniedVerbsStr.equals("")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					domain.setUseOnlyJson(true);
					domain.setVerbDeniesStr(deniedVerbsStr);
					domain.setLanguage(project.getLanguage());
					domain.setDbType(project.getDbType());
					domain.setFrontendUi(project.getFrontendUi());
					domain.setBackendUi(project.getBackendUi());
					domain.setComputerLanguage(project.getComputerLanguage());
					domain.setTechnicalstack(project.getTechnicalstack());
					domains.add(domain);
				}else if (sheet.getSheetName().toLowerCase().contains("enum")) {
					FrontEnum myenum = translateEnum(sheet);
					String deniedVerbsStr = myenum.getVerbDeniesStr();
					if (deniedVerbsStr.equals("max")||deniedVerbsStr.startsWith("max+")) {
					}else if (deniedVerbsStr.equals("none")) {
						deniedVerbsStr = "ExportWord,ExportPPT,FilterWord,FilterPPT";
					} else if (deniedVerbsStr.endsWith(",")||StringUtil.isBlank(deniedVerbsStr)) deniedVerbsStr += "ExportWord,ExportPPT,FilterWord,FilterPPT";
					else deniedVerbsStr += ",ExportWord,ExportPPT,FilterWord,FilterPPT";
					myenum.setUseOnlyJson(true);
					myenum.setVerbDeniesStr(deniedVerbsStr);
					myenum.setLanguage(project.getLanguage());
					myenum.setDbType(project.getDbType());
					myenum.setFrontendUi(project.getFrontendUi());
					myenum.setBackendUi(project.getBackendUi());
					myenum.setComputerLanguage(project.getComputerLanguage());
					myenum.setTechnicalstack(project.getTechnicalstack());
					domains.add(myenum);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		
		for (FrontDomain d:domains) {
			if (!StringUtil.isBlank(project.getControllerSuffix())) d.setControllerSuffix(project.getControllerSuffix());	
			if (!StringUtil.isBlank(project.getControllerNamingSuffix())) d.setControllerNamingSuffix(project.getControllerNamingSuffix());
			if (!StringUtil.isBlank(project.getTitle())) d.setTitle(project.getTitle());
			if (!StringUtil.isBlank(project.getStandardName())) d.setProjectName(project.getStandardName());
		}
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("module")) {
					org.javaforever.gatescore.core.Module m = translateModule(project, sheet, domains);
					project.addModule(m);
				}
				if (sheet.getSheetName().toLowerCase().contains("layout")) {
					LayoutComb lcm = translateLayout(project, sheet, domains);
					project.addLayoutComb(lcm);
				}
				if (sheet.getSheetName().toLowerCase().contains("report")) {
					ReportComb rcm = translateReport(project, sheet, domains);
					project.addReportComb(rcm);
				}
			}catch (ValidateException ve) {
				info0.addAllCompileErrors(ve.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(ve.getValidateInfo().getCompileWarnings());
			}
		}
		
		if (!info0.success(true)) {
			System.out.println(info0.toString());
			throw new ValidateException(info0);
		}
		
		decorateDropdowns(domains);
		List<FrontDomain> domainList = new ArrayList<>();
		domainList.addAll(domains);
		decorateMtmDomainList(project.getDomains(),domainList);
		project.setDomains(domainList);

		List<FrontPrism> prismList = generatePrismsByDomains(domainList);
		project.setPrisms(prismList);
		
		for (FrontPrism p : project.getPrisms()) {
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (FrontManyToMany mtm:p.getDomain().getManyToManies()){
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}
		
		project.setPjg(new PackageJsonGenerator(domains.get(0)));
		return project;
	}
	
	public ReportComb translateReport(FrontProject project, HSSFSheet sheet, List<FrontDomain> domains) throws Exception {
		ReportExcelTranslator ret = new ReportExcelTranslator();
		return ret.translate(project, sheet, domains);
	}
	
	public LayoutComb translateLayout(FrontProject project, HSSFSheet sheet, List<FrontDomain> domains) throws Exception {
		LayoutExcelTranslator let = new LayoutExcelTranslator();
		return let.translate(project, sheet, domains);
	}
	
	public org.javaforever.gatescore.core.Module translateModule(FrontProject project, HSSFSheet sheet, List<FrontDomain> domains) throws Exception {
		String moduleName = readMetaField(sheet, "module");
		if ("ShiroAuth".equalsIgnoreCase(moduleName)||"SimpleAuth".equalsIgnoreCase(moduleName)) {
			ShiroAuthExcelTranslator sat = new ShiroAuthExcelTranslator();
			return sat.translate(project, sheet, domains);
		}
		return null;
	}

	private List<FrontPrism> generatePrismsByDomains(List<FrontDomain> domainList) throws Exception{
		List<FrontPrism> prisms = new ArrayList<FrontPrism>();
		for (FrontDomain d : domainList) {
			if (d instanceof FrontEnum) {
				FrontEnum myenum = (FrontEnum) d;
				FrontDummyPrism p = new FrontDummyPrism();
				p.setPackageToken(myenum.getPackageToken());
				p.setStandardName(myenum.getCapFirstDomainName() + "Prism");
				p.setDomain(myenum);
				p.setProjectDomains(domainList);
				p.generateDummyPrismFromEnum();
				prisms.add(p);
			}else {
				FrontPrism p = new FrontPrism();
				p.setPackageToken(d.getPackageToken());
				p.setStandardName(d.getCapFirstDomainName() + "Prism");
				p.setDomain(d);
				p.setProjectDomains(domainList);
				p.generatePrismFromDomain();
				prisms.add(p);
			}
		}
		return prisms;
	}

	public FrontProject translateProjectMetaData(HSSFSheet metaSheet, FrontProject project) {
		String dbtype = readMetaField(metaSheet, "dbtype");
		String projectName = readMetaField(metaSheet, "project");
		String packageToken = readMetaField(metaSheet, "packagetoken");
		String dbprefix = readMetaField(metaSheet, "dbprefix");
		String dbname = readMetaField(metaSheet, "dbname");
		String dbusername = readMetaField(metaSheet, "dbusername");
		String dbpassword = readMetaField(metaSheet, "dbpassword");
		String title = readMetaField(metaSheet, "title");
		String subTitle = readMetaField(metaSheet, "subtitle");
		String footer = readMetaField(metaSheet, "footer");
		String crossOrigin = readMetaField(metaSheet, "crossorigin");
		String resolution = readMetaField(metaSheet, "resolution");
		String technicalstack = readMetaField(metaSheet, "technicalstack");
		String controllersuffix= readMetaField(metaSheet, "controllersuffix").toLowerCase();
		String controllernamingsuffix= StringUtil.capFirst(readMetaField(metaSheet, "controllernamingsuffix"));
		String language = StringUtil.capFirst(readMetaField(metaSheet, "language"));
		String frontBaseApi = StringUtil.capFirst(readMetaField(metaSheet, "frontbaseapi"));
		String frontendUi = readMetaField(metaSheet, "frontendUi");
		String backendUi = readMetaField(metaSheet, "backendUi");
		String computerLanguage = StringUtil.capFirst(readMetaField(metaSheet, "computerLanguage"));
		if ("".equalsIgnoreCase(computerLanguage)) computerLanguage = "java";
		
		project.setStandardName(projectName);
		project.setBackendProjectName(projectName);
		project.setLanguage(language);	
		project.setTechnicalstack(technicalstack);
		project.setTitle(title);
		project.setDbType(dbtype);
		project.setFrontendUi(frontendUi);
		project.setBackendUi(backendUi);
		project.setComputerLanguage(computerLanguage);
		
		if (!StringUtil.isBlank(controllersuffix)) project.setControllerSuffix(controllersuffix);
		if (!StringUtil.isBlank(controllernamingsuffix)) project.setControllerNamingSuffix(controllernamingsuffix);
		if (!StringUtil.isBlank(frontBaseApi)) project.setFrontBaseApi(frontBaseApi);
		return project;
	}

	public FrontPrism translatePrism(HSSFSheet prismSheet) {
		FrontPrism prism = new FrontPrism();
		return prism;
	}

	public FrontDomain translateDomain(HSSFSheet domainSheet) throws ValidateException {
		FrontDomain domain = new FrontDomain();
		String domainName = readMetaField(domainSheet, "domain");
		if (domainName.contains("_")) domainName = StringUtil.capFirst(StringUtil.changeTableColumtoDomainField(domainName));
		String plural = readMetaField(domainSheet, "plural");
		if (plural.contains("_")) plural = StringUtil.capFirst(StringUtil.changeTableColumtoDomainField(plural));
		String domainlabel = readMetaField(domainSheet, "domainlabel");
		String verdeniesStr = readMetaField(domainSheet, "verbdenies");
		Cell metaFieldCell = locateKeyCell(domainSheet, "元字段类型");
		Cell fieldCell = locateKeyCell(domainSheet, "字段");
		Cell fieldTypeCell = locateKeyCell(domainSheet, "字段类型");
		Cell fieldFixNameCell = locateKeyCell(domainSheet, "固定名字");
		int fieldFixNameRow = fieldFixNameCell == null ? -1 : fieldFixNameCell.getRowIndex();
		Cell fieldLabelCell = locateKeyCell(domainSheet, "字段标签");
		Cell fieldDataCell = locateKeyCell(domainSheet, "数据");
		ValidateInfo info = new ValidateInfo();
		for (int i = metaFieldCell.getColumnIndex() + 1; i < domainSheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			try {
				String metaField = readFieldMeta(domainSheet, i, metaFieldCell.getRowIndex());
				if (!StringUtil.isBlank(metaField)
						&& (metaField.equalsIgnoreCase("field") || metaField.equalsIgnoreCase("id")
								|| metaField.equalsIgnoreCase("domainid") || metaField.equalsIgnoreCase("domainname")
								|| metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
					FrontField f = readDomainField(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex(),fieldFixNameRow, domain);
					if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("field")) {
						domain.addField(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("id") || metaField.equalsIgnoreCase("domainid"))) {
						domain.setDomainId(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
						domain.setActive(f);
					} else if (!StringUtil.isBlank(metaField) && (metaField.equalsIgnoreCase("domainname")
							|| metaField.equalsIgnoreCase("activefield"))) {
						domain.setDomainName(f);
					}
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("dropdown")) {
					FrontDropdown dp = readDropdown(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					domain.addField(dp);
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("manytomanyslave")) {
					String mtmname = readManyToManyName(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAlias = readManyToManyAlias(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAliasLabel = readManyToManyAliasLabel(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					FrontManyToMany mtm = new FrontManyToMany(domainName,mtmname);
					if (!StringUtil.isBlank(mtmAlias)){
						mtm.setSlaveAlias(mtmAlias);
						mtm.setSlaveAliasLabel(mtmAliasLabel);
						mtm.setStandardName("Link"+StringUtil.capFirst(domainName)+StringUtil.capFirst(mtmAlias));
					}
					domain.addManyToMany(mtm);
				}
			} catch (ValidateException e) {
				info.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		if (!info.success(false))
			throw new ValidateException(info);
		domain.setStandardName(domainName);
		if (!StringUtil.isBlank(plural))
			domain.setPlural(plural);
		if (!StringUtil.isBlank(domainlabel))
			domain.setLabel(domainlabel);
		if (!StringUtil.isBlank(verdeniesStr))
			domain.setVerbDeniesStr(verdeniesStr);

		return domain;
	}
	
	public FrontEnum translateEnum(HSSFSheet domainSheet) throws ValidateException {
		FrontEnum myenum = new FrontEnum();
		String domainName = readMetaField(domainSheet, "domain");
		if (domainName.contains("_")) domainName = StringUtil.capFirst(StringUtil.changeTableColumtoDomainField(domainName));
		String plural = readMetaField(domainSheet, "plural");
		if (plural.contains("_")) plural = StringUtil.capFirst(StringUtil.changeTableColumtoDomainField(plural));
		String domainlabel = readMetaField(domainSheet, "domainlabel");
		String verdeniesStr = readMetaField(domainSheet, "verbdenies");
		Cell metaFieldCell = locateKeyCell(domainSheet, "元字段类型");
		Cell fieldCell = locateKeyCell(domainSheet, "字段");
		Cell fieldTypeCell = locateKeyCell(domainSheet, "字段类型");
		Cell fieldFixNameCell = locateKeyCell(domainSheet, "固定名字");
		int fieldFixNameRow = fieldFixNameCell == null ? -1 : fieldFixNameCell.getRowIndex();
		Cell fieldLabelCell = locateKeyCell(domainSheet, "字段标签");
		Cell fieldDataCell = locateKeyCell(domainSheet, "数据");
		ValidateInfo info = new ValidateInfo();
		for (int i = metaFieldCell.getColumnIndex() + 1; i < domainSheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			try {
				String metaField = readFieldMeta(domainSheet, i, metaFieldCell.getRowIndex());
				if (!StringUtil.isBlank(metaField)
						&& (metaField.equalsIgnoreCase("field") || metaField.equalsIgnoreCase("id")
								|| metaField.equalsIgnoreCase("domainid") || metaField.equalsIgnoreCase("domainname")
								|| metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
					FrontField f = readDomainField(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex(),fieldFixNameRow, myenum);
					if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("field")) {
						myenum.addField(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("id") || metaField.equalsIgnoreCase("domainid"))) {
						myenum.setDomainId(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
						myenum.setActive(f);
					} else if (!StringUtil.isBlank(metaField) && (metaField.equalsIgnoreCase("domainname")
							|| metaField.equalsIgnoreCase("activefield"))) {
						myenum.setDomainName(f);
					}
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("dropdown")) {
					FrontDropdown dp = readDropdown(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					myenum.addField(dp);
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("manytomanyslave")) {
					String mtmname = readManyToManyName(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAlias = readManyToManyAlias(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAliasLabel = readManyToManyAliasLabel(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					FrontManyToMany mtm = new FrontManyToMany(domainName,mtmname);
					if (!StringUtil.isBlank(mtmAlias)){
						mtm.setSlaveAlias(mtmAlias);
						mtm.setSlaveAliasLabel(mtmAliasLabel);
						mtm.setStandardName("Link"+StringUtil.capFirst(domainName)+StringUtil.capFirst(mtmAlias));
					}
					myenum.addManyToMany(mtm);
				}
			} catch (ValidateException e) {
				info.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		if (!info.success(false))
			throw new ValidateException(info);
		myenum.setStandardName(domainName);
		if (!StringUtil.isBlank(plural))
			myenum.setPlural(plural);
		if (!StringUtil.isBlank(domainlabel))
			myenum.setLabel(domainlabel);
		if (!StringUtil.isBlank(verdeniesStr))
			myenum.setVerbDeniesStr(verdeniesStr);

		return myenum;
	}

	public FrontField readDomainField(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex, int fieldFixNameIndex,FrontDomain domain) throws ValidateException {
		FrontField f = new FrontField();
		String metafield = readFieldMeta(sheet, columIndex, metaFieldIndex);
		String fieldname = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim();
		if (fieldname.contains("_")) fieldname = StringUtil.changeTableColumtoDomainField(fieldname);
		String fixname = fieldFixNameIndex < 0 ? ""
				: sheet.getRow(fieldFixNameIndex).getCell(columIndex).getStringCellValue().trim().replace("\'", "");
		
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldLabel = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim();

		ValidateInfo info = new ValidateInfo();
		if (!StringUtil.isLowerCaseLetter(fieldname)) {
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "未使用小写英文字母开头！");
		}
		if (fieldname.length() >= 2 && !StringUtil.isLowerCaseLetterPosition(fieldname,1)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "第二个字母未使用小写英文字母！");
		}
		if (isForbidden(fieldname)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "使用了被禁止的单词！");
		}
		if (isSqlKeyword(fieldname)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "使用了SQL关键字！");
		}
		
		if (!StringUtil.isBlank(metafield)
				&& (metafield.equalsIgnoreCase("id") || metafield.equalsIgnoreCase("domianid")
						|| metafield.equalsIgnoreCase("domainname") || metafield.equalsIgnoreCase("active")
						|| metafield.equalsIgnoreCase("activefield") || metafield.equalsIgnoreCase("field"))) {
			if (!StringUtil.isBlank(fieldType))
				f.setFieldType(new FrontType(fieldType));
			if (!StringUtil.isBlank(fieldLabel)) {
				f.setLabel(fieldLabel);
				domain.putFieldLabel(fieldname, fieldLabel);
			}
			if (!StringUtil.isBlank(fieldname)) {
				f.setFieldName(fieldname);
				f.setFixedName(fieldname);
			}
			if (!StringUtil.isBlank(fixname)) {
				f.setFixedName(fixname);
			} 
			if (!info.success(false))
				throw new ValidateException(info);
			else
				return f;
		} else {
			info.addCompileError("字段解析错误");
			throw new ValidateException(info);
		}
	}

	private boolean isSqlKeyword(String fieldname) {
		for (String word : sqlKeyWords) {
			if (word.equals(fieldname))
				return true;
		}
		return false;
	}

	private boolean isForbidden(String fieldname) {
		for (String word : forbiddenwords) {
			if (word.equals(fieldname))
				return true;
		}
		return false;
	}

	public FrontDropdown readDropdown(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String fieldname = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim();
		if (fieldname.contains("_")) fieldname = StringUtil.changeTableColumtoDomainField(fieldname);
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldLabel = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim();
		ValidateInfo info = new ValidateInfo();
		if (!StringUtil.isLowerCaseLetter(fieldname)) {
			info.addCompileError("下拉列表字段" + fieldname + "未使用小写英文字母开头！");
		}
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("dropdown")) {
			FrontDropdown dp = new FrontDropdown(fieldType);
			dp.setAliasName(fieldname);
			dp.setFieldName(dp.getAliasName());
			dp.setLabel(fieldLabel);
			if (!info.success(false))
				throw new ValidateException(info);
			else
				return dp;
		} else {
			info.addCompileError("字段解析错误");
			throw new ValidateException(info);
		}
	}

	public String readFieldMeta(HSSFSheet sheet, int columIndex, int metaFieldIndex) {
		String metafield = getCellStringValue(sheet.getRow(metaFieldIndex).getCell(columIndex));
		return metafield;
	}

	public String readManyToManyName(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim();
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return fieldType;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}
	
	public String readManyToManyAlias(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String field = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim();
		if (field.contains("_")) field = StringUtil.capFirst(StringUtil.changeTableColumtoDomainField(field));
		field = StringUtil.capFirst(field);
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return field;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}
	
	public String readManyToManyAliasLabel(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String field = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim();
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return field;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}

	public String readMetaField(HSSFSheet metaSheet, String key) {
		Cell c = locateKeyCell(metaSheet, key);
		if (c == null) {
			return "";
		}else {
			return this.getCellStringValue(metaSheet.getRow(c.getRowIndex()).getCell(c.getColumnIndex() + 1)).trim();
		}
	}

	public Cell locateKeyCell(HSSFSheet metaSheet, String key) {
		int rowbegin = metaSheet.getFirstRowNum();
		int rowend = metaSheet.getLastRowNum();
		for (int i = rowbegin; i <= rowend; i++) {
			Row r = metaSheet.getRow(i);
			if (r != null) {
				for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) {
					Cell c = r.getCell(j);
					if (c != null && this.getCellStringValue(c).equalsIgnoreCase(key))
						return c;
				}
			}
		}
		return null;
	}

	public List<FrontDomain> readDomainListWithData(HSSFSheet sheet, FrontDomain templateDomain) throws Exception {
		List<FrontDomain> resultList = new ArrayList<FrontDomain>();
		Cell metaCell = locateKeyCell(sheet, "元字段类型");
		Cell dataCell = locateKeyCell(sheet, "数据");
		Cell fieldCell = locateKeyCell(sheet, "字段");
		for (int i = dataCell.getRowIndex(); i < findOutLastDataRowIndex(sheet, findOutIdColIndex(sheet),
				dataCell.getRowIndex()); i++) {
			for (FrontField f : templateDomain.getFields()) {
				if (f instanceof FrontDropdown) {
					FrontDropdown dp = (FrontDropdown) f;
					String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, dp.getAliasName(),
							fieldCell.getColumnIndex() + 1, metaCell.getRowIndex(),fieldCell.getRowIndex(), i));
					if (!StringUtil.isBlank(fieldValue)) {
						templateDomain.setFieldValue(dp.getAliasName(), fieldValue);
					}
					else
						templateDomain.setFieldValue(dp.getAliasName(), fieldValue);
				} else {
					String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, f.getFieldName(),
							fieldCell.getColumnIndex() + 1,metaCell.getRowIndex(), fieldCell.getRowIndex(), i));
					if (!StringUtil.isBlank(fieldValue))
						templateDomain.getField(f.getFieldName()).setFieldValue(fieldValue);
					else
						templateDomain.getField(f.getFieldName()).setFieldValue("");
				}
			}
			for (FrontManyToMany mtm:templateDomain.getManyToManies()){
				String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, mtm.getSlaveAlias(),
						fieldCell.getColumnIndex() + 1, metaCell.getRowIndex(),fieldCell.getRowIndex(), i));
				
				mtm.setMaster(templateDomain);					
				mtm.setValues(fieldValue);
				mtm.setMasterValue(templateDomain.getDomainId().getFieldValue());
			}
			resultList.add(templateDomain);
		}
		return resultList;
	}
	
	public void decorateMtmDomainList(List<FrontDomain> domains,List<FrontDomain> allDomains) throws Exception{
		for (FrontDomain targetDomain: domains){
			for (FrontManyToMany mtm:targetDomain.getManyToManies()){
				mtm.setSlave(findDomainFromListByStandardName(allDomains,mtm.getManyToManySalveName()));
			}
		}
	}	

	public static FrontDomain findDomainFromSetByStandardName(Set<FrontDomain> domains, String standardName)
			throws ValidateException {
		for (FrontDomain d : domains) {
			if (d.getStandardName().equals(standardName))
				return d;
		}
		ValidateInfo info = new ValidateInfo();
		info.addCompileError("在域对象列表找不到域对象" + standardName + "。");
		throw new ValidateException(info);
	}	

	public static void decorateDropdowns(List<FrontDomain> domains) throws ValidateException {
		for (FrontDomain d : domains) {
			for (FrontField f : d.getAllFieldsWithoutId()) {
				if (f instanceof FrontDropdown) {
					FrontDropdown dp = (FrontDropdown) f;
					FrontDomain t = DomainUtil.findDomainInList(domains, dp.getTargetName());
					dp.decorate(t);
				}
			}
		}
	}

	private FrontDomain findDomainFromListByStandardName(List<FrontDomain> domainList, String standardName) throws Exception {
		for (FrontDomain d : domainList) {
			if (d.getStandardName().equals(standardName))
				return d;
		}
		ValidateInfo info = new ValidateInfo();
		info.addCompileError("在域对象列表找不到域对象" + standardName + "。");
		throw new ValidateException(info);
	}

	public int findOutIdColIndex(HSSFSheet sheet) {
		Cell metaFieldCell = locateKeyCell(sheet, "元字段类型");
		for (int i = metaFieldCell.getColumnIndex() + 1; i < sheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			if (sheet.getRow(metaFieldCell.getRowIndex()).getCell(i).getStringCellValue().equals("id")) {
				return i;
			}
		}
		return metaFieldCell.getColumnIndex() + 1;
	}

	public int findOutLastDataRowIndex(HSSFSheet sheet, int idColIndex, int beginRowIndex) {
		for (int i = beginRowIndex; i <= sheet.getLastRowNum(); i++) {
			if (sheet.getRow(i)==null ||sheet.getRow(i).getCell(idColIndex)==null || StringUtil.isBlank(getCellStringValue(sheet.getRow(i).getCell(idColIndex))))
				return i;
		}
		return sheet.getLastRowNum()+1;
	}

	public String readDomainFieldValue(HSSFSheet sheet, String fieldName, int beginColIndex,int metaFieldRowIndex, int fieldNameRowIndex,
			int rowIndex) throws ValidateException{
		for (int i = beginColIndex; i < sheet.getRow(fieldNameRowIndex).getLastCellNum(); i++) {
			Cell metac = sheet.getRow(metaFieldRowIndex).getCell(i);
			Cell c = sheet.getRow(fieldNameRowIndex).getCell(i);
			String metaField = metac.getStringCellValue();
			String cellfieldName = c.getStringCellValue();
			if (cellfieldName.contains("_")) cellfieldName = StringUtil.changeTableColumtoDomainField(cellfieldName);
			if (metaField.equalsIgnoreCase("manytomanyslave")) {
				cellfieldName = StringUtil.capFirst(cellfieldName);
			}else {
				cellfieldName = StringUtil.lowerFirst(cellfieldName);
			}
			if (!StringUtil.isBlank(cellfieldName) && cellfieldName.equals(fieldName)) {
				return getCellStringValue(sheet.getRow(rowIndex).getCell(i));
			}
		}
		return "";
	}

	public String getCellStringValue(Cell c) {
		if (c.getCellType() == CellType.STRING)
			return c.getStringCellValue();
		else if (c.getCellType() == CellType.NUMERIC) {
			short format = c.getCellStyle().getDataFormat();
			if (format == 14 || format == 31 || format == 57 || format == 58) {
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			} else if (format == 20 || format == 32) {
				DateFormat formater = new SimpleDateFormat("HH:mm");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			}
			double dis = c.getNumericCellValue() - Math.round(c.getNumericCellValue());
			if (dis > 0.0001d) {
				return "" + c.getNumericCellValue();
			} else {
				return "" + (long) Math.round(c.getNumericCellValue());
			}
		} else if (c.getCellType() == CellType.BOOLEAN) {
			return "" + c.getBooleanCellValue();
		}else if (c.getCellType() == CellType.BOOLEAN) {
			return "" + BooleanUtil.parseBooleanInt("" + c.getBooleanCellValue());
		}else if (c.getCellType() == CellType.FORMULA) {
				String fml = c.getCellFormula();
				if (fml.equalsIgnoreCase("TRUE")) return "true";
				else if (fml.equalsIgnoreCase("FALSE")) return "false";
				else return "";
		}else {
			return "";
		}
	}
	
	public static FrontDomain decorateDomainDropdowns(FrontDomain domain,List<FrontDomain> domainList) throws ValidateException {
		for (FrontField f : domain.getFieldsWithoutId()) {
			if (f instanceof FrontDropdown) {
				FrontDropdown dp = (FrontDropdown) f;
				FrontDomain t = DomainUtil.findDomainInList(domainList, dp.getTargetName());
				dp.decorate(t);
			}
		}
		return domain;
	}

	public static void main(String[] args) {
		try {
			InputStream is = new FileInputStream("");
			POIFSFileSystem fs = new POIFSFileSystem(is);
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			SpreadSheetTranslater pwb = new SpreadSheetTranslater();
			FrontProject pj = pwb.translate(wb);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
